from DataAccess.DBConnFactory import *
from datetime import date
import numpy as np

from string import Template

from Common.TimeSeries import *

from Misc.Utils import format_sql_criteria
		
def query_stock_bar(date_list, tick_list):
	
	sql_tpl = Template('''select TDATE,
							SYMBOL,
							TOPEN,
							TCLOSE,
							HIGH,
							LOW,
							PCHG,
							TURNOVER,
							NDEALS,
							VOTURNOVER,
							VATURNOVER
						from CHDQUOTE
						where ${TICK_CRITERIA} 
						and TDATE>='${START}' 
						and TDATE<='${END}'
						order by TDATE,SYMBOL''')
						
	sql_text = sql_tpl.substitute(TICK_CRITERIA = format_sql_criteria('SYMBOL', tick_list),
									START = np.min(date_list).strftime('%Y%m%d'),
									END = np.max(date_list).strftime('%Y%m%d'))
	cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()
	cursor.execute(sql_text)
	r = cursor.fetchall()
	
	if r:
		field_list = ['OPEN', 'CLOSE', 'HIGH', 'LOW', 'RETURN', 'TURNOVER', 'DEALS', 'VOLUME', 'AMOUNT']
		value_array = np.empty((len(date_list), len(tick_list), len(field_list)))
		
		for elem in r:
			date_str = elem[0]
			d = date(date_str[:4], date_str[4:6], date_str[-2:])
			
			if d in date_list and elem[1] in tick_list:
			
				i = date_list.index(d)
				j = tick_list.index(elem[1])
				value_array[i, j] = elem[2:]
			
			return TimeSeries(date_list, tick_list, field_list, value_array)	
	else:
		print 'Err - failed in querying stock bar'
		
def query_index_bar(date_list, tick_list):
	
	sql_tpl = Template('''select TDATE,
							SYMBOL,
							TOPEN,
							TCLOSE,
							HIGH,
							LOW,
							PCHG,
							VOTURNOVER,
							VATURNOVER
						from CIHDQUOTE
						where ${TICK_CRITERIA} 
						and TDATE>='${START}' 
						and TDATE<='${END}'
						order by TDATE,SYMBOL''')
						
	sql_text = sql_tpl.substitute(TICK_CRITERIA = format_sql_criteria('SYMBOL', tick_list),
									START = np.min(date_list).strftime('%Y%m%d'),
									END = np.max(date_list).strftime('%Y%m%d'))

	cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()
	cursor.execute(sql_text)
	r = cursor.fetchall()
	
	if r:
		field_list = ['OPEN', 'CLOSE', 'HIGH', 'LOW', 'RETURN', 'VOLUME', 'AMOUNT']
		value_array = np.empty((len(date_list), len(tick_list), len(field_list)))
		
		for elem in r:
			date_str = str(elem[0])
			d = date(int(date_str[:4]), int(date_str[4:6]), int(date_str[-2:]))
			
			if d in date_list and elem[1] in tick_list:
				i = date_list.index(d)
				j = tick_list.index(elem[1])
				value_array[i, j] = elem[2:]
			
		return TimeSeries(date_list, tick_list, field_list, value_array)
		
	else:
		print 'Err - failed in querying index bar'
	
	
	
	
	
	
	